/*\
 * Copyright 2012 Rob van de Werken
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
\*/

package nl.RvdW.TVgids;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.zip.GZIPInputStream;

import android.app.ProgressDialog;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v4.app.Fragment;
import android.util.Log;
import android.widget.Toast;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 1;
	public static final String DATABASE_NAME = "TVgids";
	private SQLiteDatabase mDb;
	private final Context mCtx;
	
	DatabaseHelper(Context context) {
		super(context,DATABASE_NAME,null,DATABASE_VERSION);
		this.mCtx = context;
		mDb=null;
		open();
	}
	
	public void open() {
		if(mDb==null){
			mDb = this.getWritableDatabase();
			fillTabs();
		}
	}

	public void close(){
		clearTabs();
		super.close();
		mDb=null;
	}

	private synchronized Cursor query(String q,String[] p){
		Cursor result;
		result=mDb.rawQuery(q,p);
		result.moveToFirst();
		return result;
	}

	private synchronized void query(LinkedList<String> q){
		mDb.beginTransaction();
		for(String s:q){
			mDb.execSQL(s);
		}
		mDb.setTransactionSuccessful();
		mDb.endTransaction();
	}

	public Programme convertCursor(Cursor q){
		return new Programme(q.getInt(0),q.getInt(1),q.getInt(2),q.getInt(3),q.getString(4),q.getString(5),q.getString(6));
	}
	
	public void fetchChannel(ArrayList<Programme> programs, String chan){
		if(mDb==null || !mDb.isOpen()){
			Log.d(Global.Name,"Database not open!");
			return;
		}
		String time = Long.toString(System.currentTimeMillis() / 1000L);
		try {
			Cursor q;
			//q = mDb.rawQuery("SELECT * FROM 'programme' WHERE channel=? AND ?<stop", new String[] {chan,time}); //Sort by id, channels.lst order
			q = query("SELECT * FROM 'programme' WHERE channel=? AND ?<stop", new String[] {chan,time}); //Sort by id, channels.lst order
			programs.clear();
			while (!q.isAfterLast()) {
				programs.add(convertCursor(q));
				q.moveToNext();
			}
		}catch (Exception e){
			Log.d(Global.Name,e.getMessage());
		}
	}
	
	public void fetchCurrent(ArrayList<Programme> programs,boolean current) {
		if(mDb==null || !mDb.isOpen()){
			Log.d(Global.Name,"Database not open!");
			return;
		}
		String time = Long.toString(System.currentTimeMillis() / 1000L);
		try {
			Cursor q;
			if(current){
				q = query("SELECT p.id,p.start,p.stop,p.channel,p.category,p.title,p.description,c.sort FROM (SELECT * FROM programme WHERE start<=? AND ?<stop ORDER BY id DESC) AS p, channels AS c WHERE p.channel=c.id GROUP BY p.channel ORDER BY sort", new String[] {time,time});
			}else{
				q = query("SELECT p.id,p.start,p.stop,p.channel,p.category,p.title,p.description,c.sort FROM (SELECT * FROM programme WHERE start>? ORDER BY id DESC) AS p, channels AS c WHERE p.channel=c.id GROUP BY p.channel ORDER BY sort", new String[] {time});
			}
			programs.clear();
			while (!q.isAfterLast()) {
				programs.add(convertCursor(q));
				q.moveToNext();
			}
		}catch (Exception e){
			Log.d(Global.Name,e.getMessage());
		}
	}

	public Programme getProgramme(int id){
		if(!mDb.isOpen()){
			Log.d(Global.Name,"Database not open!");
			return null;
		}
		
		Cursor q = query("SELECT * FROM 'programme' WHERE id=?", new String[] {Integer.toString(id)});
		return convertCursor(q);
	}
	
	private class updateTask extends AsyncTask<String,Void,String>{
		ProgressDialog dialog;
		
		protected void onPreExecute(){
			dialog = ProgressDialog.show(mCtx,"","Updating...",true);
		}
		
		protected String doInBackground(String... param) {
			String result=null;

			if(!mDb.isOpen()){
				Log.d(Global.Name,"Database not open!");
				return "Database not open!";
			}

			try{
				URL url = new URL(param[0]);
				URLConnection ucon = url.openConnection();
				BufferedReader br = new BufferedReader(new InputStreamReader(new GZIPInputStream(new BufferedInputStream(ucon.getInputStream()))));
				String line;
				LinkedList<String> q = new LinkedList<String>();
				while((line=br.readLine())!=null) {
					if(!line.equals("BEGIN TRANSACTION;") && !line.equals("COMMIT;")){
						q.add(line.replace(';',' '));
					}
				}
				query(q);
				result="Update successful.";
			} catch(Exception e) {
				result="Error while updating:\n"+e.getMessage();
			}
			return result;
		}
		
		protected void onPostExecute(String result){
			// Reset to first tab
			Global.tabs.setCurrentTab(0);
			Global.viewpager.setCurrentItem(0);

			Toast.makeText(mCtx, result, Toast.LENGTH_LONG).show();
			close();
			open();
			dialog.dismiss();
		}
	}

	public void update() {
		if(!Global.isOnline()){
			Toast.makeText(mCtx, "Network connection missing.", Toast.LENGTH_LONG).show();
			return;
		}
		new updateTask().execute("http://home.a-eskwadraat.nl/~rwerken/tvgids/tvgids.sql.gz");
	}

	public static void clearTabs() {
		Global.tabs.clearAllTabs();
		Global.fragments.clear();
	}
	
	public void fillTabs() {
		// Add 'current' to the tabs
		addCur();
		addNext();

		// Add channels to the tabs
		if(mDb==null || !mDb.isOpen()){
			Log.d(Global.Name,"Database not open!");
			return;
		}
		try {
			Cursor q = query("SELECT id,name FROM 'channels'", null);
			while (!q.isAfterLast()) {
				Bundle b = new Bundle();
				b.putString("id", q.getString(0));
				b.putString("name", q.getString(1).substring(3));
				
				Fragment frag;
				frag = Fragment.instantiate(Global.context,
						ChannelFragment.class.getName());
				frag.setArguments(b);
				Global.addTab(frag, b.getString("id"), b.getString("name"));

				q.moveToNext();
			}
		}catch (Exception e){
			Log.d(Global.Name,e.getMessage());
		}
	}

	public void addCur() {
		Fragment frag;
		Bundle b = new Bundle();
		frag = Fragment.instantiate(Global.context,
				TimeFragment.class.getName());
		b.putBoolean("current", true);
		b.putString("name", "Current");
		frag.setArguments(b);
		Global.addTab(frag, "current", "Current");
	}

	public void addNext() {
		Fragment frag;
		Bundle b = new Bundle();
		frag = Fragment.instantiate(Global.context,
				TimeFragment.class.getName());
		b.putBoolean("current", false);
		b.putString("name", "Next");
		frag.setArguments(b);
		Global.addTab(frag, "next", "Next");
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub
		Log.d(Global.Name,"Creating DB");
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
		Log.d(Global.Name,"Upgrading DB");
	}
}
